#include "data_store.h"
#include "SQLiteCpp/SQLiteCpp.h"
#include <iostream>
#include <opencv2/core/cuda.hpp>
#include <spdlog/spdlog.h>

namespace ai::facial_recognition {
    DataStore::DataStore(std::string db_uri): SQLITE_DB_URI(db_uri) {
        spdlog::info("SQLITE_DB_URI: {0:s}", SQLITE_DB_URI);
    }

    DataStore::~DataStore() {
    }

    int DataStore::save_face_feature(std::string face_feature, int face_feature_length) {
        try {
            // Open a database file
            SQLite::Database db(SQLITE_DB_URI, SQLite::OPEN_READWRITE);

            // Begin transaction
            SQLite::Transaction transaction(db);

            std::string sql = "INSERT INTO face_feature(face_feature, face_feature_length) VALUES (";
            sql = sql + "\"" + face_feature + "\","
                  + std::to_string(face_feature_length)
                  + ")";

            db.exec(sql);

            transaction.commit();

            return 1;
        } catch (std::exception &ex) {
            std::cout << "save_face_feature occurs exception." << std::endl;
            std::cout << ex.what() << std::endl;
        }
        return 0;
    }


    std::vector<FaceFeatureInfo> DataStore::get_all_face_features() {
        std::vector<FaceFeatureInfo> infos;
        try {
            // Open a database file
            SQLite::Database db(SQLITE_DB_URI, SQLite::OPEN_READONLY);

            //Compile a SQL query, containing one parameter (index 1)
            SQLite::Statement query(db, "SELECT * FROM member_info");

            while (query.executeStep()) {
                FaceFeatureInfo info = {};
                info.face_feature = query.getColumn("face_feature").getString();
                info.face_feature_size = query.getColumn("face_feature_size").getInt();
                info.member_id = query.getColumn("member_id").getInt();
                info.member_name = query.getColumn("member_name").getString();

                infos.emplace_back(info);
            }
        } catch (std::exception &ex) {
            spdlog::error(ex.what());
        }

        return infos;
    }


    int DataStore::save_member_info(int member_id, std::string member_name, std::string face_feature,
                                    int face_feature_size) {
        try {
            // Open a database file
            SQLite::Database db(SQLITE_DB_URI, SQLite::OPEN_READWRITE);

            // Begin transaction
            SQLite::Transaction transaction(db);

            // 使用预编译语句来避免SQL注入
            std::string delete_sql = "DELETE FROM member_info WHERE member_id = ?";
            SQLite::Statement delete_stmt(db, delete_sql);
            delete_stmt.bind(1, member_id);
            delete_stmt.exec();

            std::string insert_sql = "INSERT INTO member_info(member_id, member_name, face_feature, face_feature_size) VALUES (?, ?, ?, ?)";
            SQLite::Statement insert_stmt(db, insert_sql);
            insert_stmt.bind(1, member_id);
            insert_stmt.bind(2, member_name);
            insert_stmt.bind(3, face_feature);
            insert_stmt.bind(4, face_feature_size);
            insert_stmt.exec();

            transaction.commit();

            return 1;
        } catch (std::exception &ex) {
            std::cout << "save_member_info occurs exception." << std::endl;
            std::cout << ex.what() << std::endl;
        }
        return 0;
    }

    int DataStore::delete_member_info(int member_id) {
        try {
            // Open a database file
            SQLite::Database db(SQLITE_DB_URI, SQLite::OPEN_READWRITE);

            // Begin transaction
            SQLite::Transaction transaction(db);

            // 使用预编译语句来避免SQL注入
            std::string delete_sql = "DELETE FROM member_info WHERE member_id = ?";
            SQLite::Statement delete_stmt(db, delete_sql);
            delete_stmt.bind(1, member_id);
            delete_stmt.exec();

            transaction.commit();

            return 1;
        } catch (std::exception &ex) {
            std::cout << "delete_member_info occurs exception." << std::endl;
            std::cout << ex.what() << std::endl;
        }
        return 0;
    }


    // 保存设备信息
    int DataStore::save_device_info(const int device_id, const std::string &device_name, const int device_type,
                                    const std::string &rtsp_url) {
        try {
            // Open a database file
            SQLite::Database db(SQLITE_DB_URI, SQLite::OPEN_READWRITE);

            // Begin transaction
            SQLite::Transaction transaction(db);

            try {
                // Use parameterized queries to avoid SQL injection
                auto delete_sql = "DELETE FROM device_info WHERE device_id = ?";
                SQLite::Statement delete_stmt(db, delete_sql);
                delete_stmt.bind(1, device_id);
                delete_stmt.exec();

                auto insert_sql =
                        "INSERT INTO device_info(device_id, device_name, device_type, rtsp_url) VALUES (?, ?, ?, ?)";
                SQLite::Statement insert_stmt(db, insert_sql);
                insert_stmt.bind(1, device_id);
                insert_stmt.bind(2, device_name);
                insert_stmt.bind(3, device_type);
                insert_stmt.bind(4, rtsp_url);
                insert_stmt.exec();

                transaction.commit();

                // Log success
                std::cout << "Device info saved successfully." << std::endl;

                return 1;
            } catch (const SQLite::Exception &ex) {
                // Rollback the transaction in case of any SQLite-specific exceptions
                transaction.rollback();
                std::cout << "save_device_info occurs SQLite exception: " << ex.what() << std::endl;
            }
        } catch (std::exception &ex) {
            std::cout << "save_device_info occurs exception." << std::endl;
            std::cout << ex.what() << std::endl;
        }
        return 0;
    }

    std::vector<DeviceInfo> DataStore::get_all_devices() {
        std::vector<DeviceInfo> infos;
        try {
            // Open a database file
            SQLite::Database db(SQLITE_DB_URI, SQLite::OPEN_READONLY);

            //Compile a SQL query, containing one parameter (index 1)
            SQLite::Statement query(db, "SELECT * FROM device_info");

            while (query.executeStep()) {
                DeviceInfo info = {};
                info.device_id = query.getColumn("device_id").getInt();
                info.device_type = query.getColumn("device_type").getInt();
                info.device_name = query.getColumn("device_name").getString();
                info.rtsp_url = query.getColumn("rtsp_url").getString();

                infos.emplace_back(info);
            }
        } catch (std::exception &ex) {
            spdlog::error("get_all_devices occurs exception: {0:s}, {1:s}.", ex.what(), SQLITE_DB_URI);
        }

        return infos;
    }
} // namespace
